﻿CREATE TABLE [dbo].[AW_Auction] (
    [AucId]           UNIQUEIDENTIFIER NOT NULL,
    [Daerah]          NVARCHAR (500)   NULL,
    [Mukim]           NVARCHAR (500)   NULL,
    [Sek]             NVARCHAR (500)   NULL,
    [JenisLot]        NVARCHAR (500)   NULL,
    [NoLot]           NVARCHAR (500)   NULL,
    [TkhPenilaian]    DATETIME         NULL,
    [LuasLot]         DECIMAL (18, 2)  NULL,
    [UnitLuas]        NVARCHAR (50)    NULL,
    [JenisHartanah]   NVARCHAR (500)   NULL,
    [Alamat]          NVARCHAR (500)   NULL,
    [Kawasan]         NVARCHAR (500)   NULL,
    [Pemberi]         NVARCHAR (500)   NULL,
    [Penerima]        NVARCHAR (500)   NULL,
    [Balasan]         DECIMAL (18, 2)  NULL,
    [CreateDate]      DATETIME         NULL,
    [CreateBy]        UNIQUEIDENTIFIER NULL,
    [StatusDelete]    TINYINT          NULL,
    [StatusLot]       NVARCHAR (50)    NULL,
    [JenisHakmilik]   NVARCHAR (50)    NULL,
    [NoHakmilik]      NVARCHAR (50)    NULL,
    [JenisPegangan]   NVARCHAR (50)    NULL,
    [TempohPajakan]   NVARCHAR (50)    NULL,
    [TkhLuputPajakan] NVARCHAR (50)    NULL,
    [JenisBangunan]   NVARCHAR (50)    NULL,
    [BilTktAtasTanah] DECIMAL (18)     NULL,
    [BilTktBwhTanah]  DECIMAL (18)     NULL,
    [BilBilikTidur]   DECIMAL (18)     NULL,
    [LuasBgnUtama]    DECIMAL (18)     NULL,
    [JenisTanaman]    NVARCHAR (50)    NULL,
    [NomborRss]       NVARCHAR (50)    NULL,
    [Grid]            NVARCHAR (50)    NULL,
    [Syer]            NVARCHAR (50)    NULL,
    CONSTRAINT [PK_AW_Auction] PRIMARY KEY CLUSTERED ([AucId] ASC)
);




GO
CREATE TRIGGER [dbo].[AW_Auction_dss_update_trigger] ON [dbo].[AW_Auction] FOR UPDATE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 1838629593 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Auction_dss_tracking] AS [target] 
USING (SELECT [i].[AucId] FROM INSERTED AS [i]) AS source([AucId]) 
ON ([target].[AucId] = [source].[AucId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[AucId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[AucId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Auction_dss_insert_trigger] ON [dbo].[AW_Auction] FOR INSERT AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 1838629593 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Auction_dss_tracking] AS [target] 
USING (SELECT [i].[AucId] FROM INSERTED AS [i]) AS source([AucId]) 
ON ([target].[AucId] = [source].[AucId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[AucId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[AucId],NULL, NULL, NULL, 0, CAST(@@DBTS AS BIGINT) + 1, NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Auction_dss_delete_trigger] ON [dbo].[AW_Auction] FOR DELETE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 1838629593 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Auction_dss_tracking] AS [target] 
USING (SELECT [i].[AucId] FROM DELETED AS [i]) AS source([AucId]) 
ON ([target].[AucId] = [source].[AucId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 1, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[AucId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[AucId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 1, GETDATE() );